using Dapper;
using Microsoft.Data.SqlClient;
using PiggyBankCashFlowApi.Repo.Entity;

namespace PiggyBankCashFlowApi.Repo;

public class CashFlowRepo(string conString, ILogger<CashFlowRepo> logger)
{
    public async Task<int> CreateCashFlowAsync(
        int userId, int subject, float amount, int direction,
        string comment, string picUrl, long transferDate)
    {
        var entity = new CashFlowEntity()
        {
            CreateDate = DateTime.UtcNow,
            LastUpdateTime = DateTime.UtcNow,
            UserId = userId,
            Subject = subject,
            Amount = amount,
            Direction = direction,
            Comment = comment,
            PicUrl = picUrl,
            TransferDate = DateTimeOffset.FromUnixTimeMilliseconds(transferDate).UtcDateTime,
        };

        await using var con = new SqlConnection(conString);
        const string cmd = """
                              INSERT INTO "TransferRecordsTable"
                                  (UserId,Subject,Amount,Direction,Comment,PicUrl,CreateDate,LastUpdateTime,TransferDate)
                              OUTPUT INSERTED.Id
                              VALUES 
                                  (@UserId,@Subject,@Amount,@Direction,@Comment,@PicUrl,@CreateDate,@LastUpdateTime, @TransferDate);
                           """;
        return await con.ExecuteScalarAsync<int>(cmd, entity);
    }

    public async Task<CashFlowEntity?> GetCashFlowAsync(int id)
    {
        await using var con = new SqlConnection(conString);
        const string cmd = "SELECT * FROM TransferRecordsTable WHERE Id = @Id;";
        var entity = await con.QuerySingleOrDefaultAsync<CashFlowEntity>(cmd, new { Id = id });
        return entity;
    }

    public async Task<int> DeleteCashFlowAsync(int id)
    {
        await using var con = new SqlConnection(conString);

        const string cmd = "DELETE FROM TransferRecordsTable WHERE Id = @Id";
        return await con.ExecuteAsync(cmd, new { Id = id });
    }

    public async Task<List<CashFlowEntity>> GetCashFlowByUserIdAsync(int[] userIds, int page , int pageSize)
    {
        var result = new List<CashFlowEntity>();
        await using var con = new SqlConnection(conString);
        var offset = (page - 1) * pageSize;
        const string cmd = """
                           SELECT * FROM TransferRecordsTable 
                           WHERE UserId In @UserIds
                           ORDER BY TransferDate DESC ,Id DESC
                           OFFSET @OffSet ROWS
                           FETCH NEXT @PageSize ROWS ONLY;
                           """;
        var entities = await con.QueryAsync<CashFlowEntity>(cmd,
            new { UserIds = userIds, Offset = offset, PageSize = pageSize });
        result.AddRange(entities);
        return result;
    }


    public async Task<int> UpdateCashFlowAsync(int id, int subject, float amount, int direction, string comment,
        string picUrl, long transferDate)
    {
        await using var con = new SqlConnection(conString);

        var entity = new
        {
            Id = id,
            Subject = subject,
            Amount = amount,
            Direction = direction,
            Comment = comment,
            PicUrl = picUrl,
            TransferDate = DateTimeOffset.FromUnixTimeMilliseconds(transferDate).UtcDateTime,
            LastUpdateTime = DateTime.UtcNow
        };
        const string cmd = """
                           UPDATE TransferRecordsTable 
                           SET Subject = @Subject,
                               Amount = @Amount,
                               Direction = @Direction,
                               Comment = @Comment, 
                               PicUrl = @PicUrl, 
                               TransferDate = @TransferDate, 
                               LastUpdateTime = @LastUpdateTime
                           WHERE Id = @Id;
                           """;
        return await con.ExecuteAsync(cmd, entity);
    }
}